const mysql = require('mysql2/promise');
require('dotenv').config();

const dbConfig = {
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME
};

async function initDatabase() {
    try {
        // 创建数据库连接
        const connection = await mysql.createConnection(dbConfig);

        // 创建数据库
        await connection.query(`
            CREATE DATABASE IF NOT EXISTS ${process.env.DB_NAME}
            DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
        `);
        console.log('Database created successfully');

        // 使用新创建的数据库
        await connection.query(`USE ${process.env.DB_NAME}`);

        // 创建用户表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS users (
                id INT PRIMARY KEY AUTO_INCREMENT,
                username VARCHAR(50) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                email VARCHAR(100) NOT NULL UNIQUE,
                avatar_url VARCHAR(255),
                bio TEXT,
                location VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        `);
        console.log('Users table created successfully');

        // 创建分类表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS categories (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(50) NOT NULL UNIQUE,
                icon VARCHAR(50),
                description TEXT,
                article_count INT DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        console.log('Categories table created successfully');

        // 创建标签表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS tags (
                id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(50) NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        console.log('Tags table created successfully');

        // 创建文章表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS articles (
                id INT PRIMARY KEY AUTO_INCREMENT,
                title VARCHAR(255) NOT NULL,
                content LONGTEXT NOT NULL,
                description TEXT,
                thumbnail_url VARCHAR(255),
                author_id INT NOT NULL,
                category_id INT,
                view_count INT DEFAULT 0,
                like_count INT DEFAULT 0,
                comment_count INT DEFAULT 0,
                status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (author_id) REFERENCES users(id),
                FOREIGN KEY (category_id) REFERENCES categories(id)
            )
        `);
        console.log('Articles table created successfully');

        // 创建文章标签关联表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS article_tags (
                article_id INT,
                tag_id INT,
                PRIMARY KEY (article_id, tag_id),
                FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
                FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
            )
        `);
        console.log('Article_tags table created successfully');

        // 创建评论表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS comments (
                id INT PRIMARY KEY AUTO_INCREMENT,
                content TEXT NOT NULL,
                article_id INT NOT NULL,
                user_id INT,
                parent_id INT,
                nickname VARCHAR(50),    
                email VARCHAR(100),      
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES users(id),
                FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);
        console.log('Comments table created successfully');

        // 插入示例数据
        try {
            // 检查用户是否已存在
            const [existingUser] = await connection.query(
                'SELECT id FROM users WHERE username = ?',
                ['凹兔基']
            );

            // 只有当用户不存在时才插入
            if (existingUser.length === 0) {
                // 插入用户
                await connection.query(`
                    INSERT INTO users (username, password, email, bio) 
                    VALUES ('凹兔基', 'hashed_password', 'example@email.com', '全栈开发工程师')
                `);
                console.log('Sample user inserted successfully');
            } else {
                console.log('User already exists, skipping insertion');
            }

            // 检查分类是否已存在
            const [existingCategory] = await connection.query(
                'SELECT id FROM categories WHERE name = ?',
                ['技术']
            );

            // 只有当分类不存在时才插入
            if (existingCategory.length === 0) {
                // 插入分类
                await connection.query(`
                    INSERT INTO categories (name, icon, description, article_count) 
                    VALUES ('技术', '💻', '技术相关文章', 12)
                `);
                console.log('Sample category inserted successfully');
            } else {
                console.log('Category already exists, skipping insertion');
            }

            // 检查并插入标签
            const sampleTags = ['JavaScript', '前端开发', '编程基础', 'Python', '数据分析'];
            for (const tagName of sampleTags) {
                const [existingTag] = await connection.query(
                    'SELECT id FROM tags WHERE name = ?',
                    [tagName]
                );

                if (existingTag.length === 0) {
                    await connection.query(
                        'INSERT INTO tags (name) VALUES (?)',
                        [tagName]
                    );
                }
            }
            console.log('Sample tags inserted successfully');

            // 获取用户ID和分类ID用于插入文章
            const [user] = await connection.query('SELECT id FROM users WHERE username = ?', ['凹兔基']);
            const [category] = await connection.query('SELECT id FROM categories WHERE name = ?', ['技术']);

            if (user.length > 0 && category.length > 0) {
                // 检查文章是否已存在
                const [existingArticle] = await connection.query(
                    'SELECT id FROM articles WHERE title = ?',
                    ['深入理解JavaScript闭包']
                );

                if (existingArticle.length === 0) {
                    // 插入文章
                    await connection.query(`
                        INSERT INTO articles (title, content, description, author_id, category_id, view_count) 
                        VALUES 
                        ('深入理解JavaScript闭包', '在JavaScript中，闭包是一个非常强大但常常被误解的概念...', '探讨JavaScript中闭包的概念、原理及实际应用场景...', ?, ?, 2300),
                        ('Docker容器化实战', 'Docker已经成为现代开发不可或缺的工具...', 'Docker容器化应用的完整指南...', ?, ?, 1800)
                    `, [user[0].id, category[0].id, user[0].id, category[0].id]);
                    console.log('Sample articles inserted successfully');

                    // 获取文章ID和标签ID用于关联
                    const [article] = await connection.query('SELECT id FROM articles WHERE title = ?', ['深入理解JavaScript闭包']);
                    const [jsTag] = await connection.query('SELECT id FROM tags WHERE name = ?', ['JavaScript']);
                    const [frontendTag] = await connection.query('SELECT id FROM tags WHERE name = ?', ['前端开发']);

                    if (article.length > 0 && jsTag.length > 0 && frontendTag.length > 0) {
                        // 插入文章标签关联
                        await connection.query(`
                            INSERT INTO article_tags (article_id, tag_id) 
                            VALUES (?, ?), (?, ?)
                        `, [article[0].id, jsTag[0].id, article[0].id, frontendTag[0].id]);
                        console.log('Sample article tags inserted successfully');
                    }
                } else {
                    console.log('Articles already exist, skipping insertion');
                }
            }

            console.log('All sample data inserted successfully');

        } catch (error) {
            console.error('Error inserting sample data:', error);
            throw error;
        }

    } catch (error) {
        console.error('Error initializing database:', error);
        process.exit(1);
    }
}

initDatabase();
